{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "import datetime\n",
    "\n",
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "booking = pd.read_sql_table('booking', engine)\n",
    "guest = pd.read_sql_table('guest', engine)\n",
    "room = pd.read_sql_table('room', engine)\n",
    "rate = pd.read_sql_table('rate', engine)\n",
    "extra = pd.read_sql_table('extra', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "Coincidence. Have two guests with the same surname ever stayed in the hotel on the evening? Show the last name and both first names. Do not include duplicates.\n",
    "\n",
    "```\n",
    "+-----------+------------+-------------+\n",
    "| last_name | first_name | first_name  |\n",
    "+-----------+------------+-------------+\n",
    "| Davies    | Philip     | David T. C. |\n",
    "| Evans     | Graham     | Mr Nigel    |\n",
    "| Howarth   | Mr George  | Sir Gerald  |\n",
    "| Jones     | Susan Elan | Mr Marcus   |\n",
    "| Lewis     | Clive      | Dr Julian   |\n",
    "| McDonnell | John       | Dr Alasdair |\n",
    "+-----------+------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>first_name_y</th>\n",
       "      <th>first_name_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>Davies</td>\n",
       "      <td>Philip</td>\n",
       "      <td>David T. C.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>Evans</td>\n",
       "      <td>Graham</td>\n",
       "      <td>Mr Nigel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>Howarth</td>\n",
       "      <td>Mr George</td>\n",
       "      <td>Sir Gerald</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Jones</td>\n",
       "      <td>Susan Elan</td>\n",
       "      <td>Mr Marcus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Lewis</td>\n",
       "      <td>Clive</td>\n",
       "      <td>Dr Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>McDonnell</td>\n",
       "      <td>John</td>\n",
       "      <td>Dr Alasdair</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     last_name first_name_y first_name_x\n",
       "387     Davies       Philip  David T. C.\n",
       "299      Evans       Graham     Mr Nigel\n",
       "253    Howarth    Mr George   Sir Gerald\n",
       "102      Jones   Susan Elan    Mr Marcus\n",
       "37       Lewis        Clive    Dr Julian\n",
       "148  McDonnell         John  Dr Alasdair"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (guest.merge(booking, left_on='id', right_on='guest_id')\n",
    "     [['last_name', 'first_name', 'booking_date', 'nights', 'id']])\n",
    "a = t.merge(t, on='last_name', how='outer').query('first_name_x != first_name_y')\n",
    "(a.loc[(((a['booking_date_x'] >= a['booking_date_y']) &\n",
    "         (a['booking_date_x'] <= a['booking_date_y'] + \n",
    "          pd.to_timedelta(a['nights_y']-1, unit='D'))) |\n",
    "        ((a['booking_date_y'] >= a['booking_date_x']) &\n",
    "         (a['booking_date_y'] <= a['booking_date_x'] + \n",
    "          pd.to_timedelta(a['nights_x']-1, unit='D')))) &\n",
    "       (a['id_x'] >= a['id_y']), :]\n",
    " [['last_name', 'first_name_y', 'first_name_x']]\n",
    " .sort_values('last_name')\n",
    " .drop_duplicates())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "Check out per floor. The first digit of the room number indicates the floor – e.g. room 201 is on the 2nd floor. For each day of the week beginning 2016-11-14 show how many rooms are being vacated that day by floor number. Show all days in the correct order.\n",
    "\n",
    "```\n",
    "+------------+-----+-----+-----+\n",
    "| i          | 1st | 2nd | 3rd |\n",
    "+------------+-----+-----+-----+\n",
    "| 2016-11-14 |   5 |   3 |   4 |\n",
    "| 2016-11-15 |   6 |   4 |   1 |\n",
    "| 2016-11-16 |   2 |   2 |   4 |\n",
    "| 2016-11-17 |   5 |   3 |   6 |\n",
    "| 2016-11-18 |   2 |   3 |   2 |\n",
    "| 2016-11-19 |   5 |   5 |   1 |\n",
    "| 2016-11-20 |   2 |   2 |   2 |\n",
    "+------------+-----+-----+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">room_no</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>floor</th>\n",
       "      <th>1st</th>\n",
       "      <th>2nd</th>\n",
       "      <th>3rd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>checkout_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-11-14</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-15</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-16</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-17</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-18</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-19</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-20</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              room_no        \n",
       "floor             1st 2nd 3rd\n",
       "checkout_date                \n",
       "2016-11-14          5   3   4\n",
       "2016-11-15          6   4   1\n",
       "2016-11-16          2   2   4\n",
       "2016-11-17          5   3   6\n",
       "2016-11-18          2   3   2\n",
       "2016-11-19          5   5   1\n",
       "2016-11-20          2   2   2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = booking.assign(checkout_date=booking['booking_date'] + \n",
    "                     pd.to_timedelta(booking['nights'], unit='D'),\n",
    "                   floor=booking['room_no'].astype(str).str.slice(0, 1))\n",
    "a = (a.loc[a['checkout_date'].between('2016-11-14', '2016-11-20')])\n",
    "a.replace({'floor': {'1': '1st', '2': '2nd', '3': '3rd', '4': '4th'}},\n",
    "          inplace=True)\n",
    "(a.groupby(['checkout_date', 'floor'])['room_no'].count().reset_index()\n",
    " .pivot(index='checkout_date', columns='floor'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "Free rooms? List the rooms that are free on the day 25th Nov 2016.\n",
    "\n",
    "```\n",
    "+-----+\n",
    "| id  |\n",
    "+-----+\n",
    "| 207 |\n",
    "| 210 |\n",
    "| 304 |\n",
    "+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id\n",
       "16  207\n",
       "19  210\n",
       "23  304"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(room.loc[~room['id'].isin(\n",
    "    booking.loc[(booking['booking_date'] <= '2016-11-25') & \n",
    "                (booking['booking_date']+pd.to_timedelta(booking['nights']-1, unit='D')\n",
    "                 >= '2016-11-25'), 'room_no'])]\n",
    " [['id']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "Single room for three nights required. A customer wants a single room for three consecutive nights. Find the first available date in December 2016.\n",
    "\n",
    "```\n",
    "+-----+------------+\n",
    "| id  | MIN(i)     |\n",
    "+-----+------------+\n",
    "| 201 | 2016-12-11 |\n",
    "+-----+------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room_no</th>\n",
       "      <th>checkout_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>201</td>\n",
       "      <td>2016-12-11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    room_no checkout_date\n",
       "23      201    2016-12-11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = booking.assign(checkout_date = booking['booking_date'] + \n",
    "                   pd.to_timedelta(booking['nights'], unit='D'))\n",
    "a = a.merge(room.loc[room['room_type']=='single'], \n",
    "            left_on='room_no', right_on='id', how='right')\n",
    "\n",
    "a = (a.loc[((a['checkout_date'] >= '2016-12-01') & \n",
    "            (a['booking_date'] <= '2016-12-31')) |\n",
    "           (a['checkout_date'] is None)]\n",
    "     .sort_values(['room_no', 'booking_date'])\n",
    "     [['booking_id', 'room_no', 'booking_date', 'nights', 'checkout_date']])\n",
    "a['next_booking'] = (a.groupby(['room_no'])['booking_date']\n",
    "                     .shift(-1).fillna('2017-01-01'))\n",
    "a['diff'] = (a['next_booking']-a['checkout_date']).dt.days.fillna(31)\n",
    "\n",
    "a.loc[(a['diff']>=3), ['room_no', 'checkout_date']].sort_values('checkout_date').iloc[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "Gross income by week. Money is collected from guests when they leave. For each Thursday in November and December 2016, show the total amount of money collected from the previous Friday to that day, inclusive.\n",
    "\n",
    "```\n",
    "+------------+---------------+\n",
    "| Thursday   | weekly_income |\n",
    "+------------+---------------+\n",
    "| 2016-11-03 |          0.00 |\n",
    "| 2016-11-10 |      12608.94 |\n",
    "| 2016-11-17 |      13552.56 |\n",
    "| 2016-11-24 |      12929.69 |\n",
    "| 2016-12-01 |      11685.14 |\n",
    "| 2016-12-08 |      13093.79 |\n",
    "| 2016-12-15 |       8975.87 |\n",
    "| 2016-12-22 |       1395.77 |\n",
    "| 2016-12-29 |          0.00 |\n",
    "| 2017-01-05 |          0.00 |\n",
    "+------------+---------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# generate Thursdays\n",
    "sdate = datetime.date(2016, 11, 1)\n",
    "edate = datetime.date(2016, 12, 31)\n",
    "days_to_thu = (3 - sdate.weekday()) % 7\n",
    "week_diff = ((edate - sdate).days - days_to_thu) // 7\n",
    "thur = pd.DataFrame(\n",
    "    {'Thursday': [sdate + datetime.timedelta(days=days_to_thu + 7 * more_wks) \n",
    "                  for more_wks in range(week_diff + 1)]})\n",
    "thur['Thursday'] = thur['Thursday'].astype('datetime64[ns]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thursday</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-11-03</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-10</th>\n",
       "      <td>12608.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-17</th>\n",
       "      <td>13552.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-11-24</th>\n",
       "      <td>12929.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-01</th>\n",
       "      <td>11685.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-08</th>\n",
       "      <td>13093.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-15</th>\n",
       "      <td>8975.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-22</th>\n",
       "      <td>1395.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-29</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              income\n",
       "Thursday            \n",
       "2016-11-03      0.00\n",
       "2016-11-10  12608.94\n",
       "2016-11-17  13552.56\n",
       "2016-11-24  12929.69\n",
       "2016-12-01  11685.14\n",
       "2016-12-08  13093.79\n",
       "2016-12-15   8975.87\n",
       "2016-12-22   1395.77\n",
       "2016-12-29      0.00"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (booking.merge(rate, left_on=['occupants', 'room_type_requested'],\n",
    "                   right_on=['occupancy', 'room_type'], how='left'))\n",
    "a['income'] = a['amount'].fillna(0) * a['nights'].fillna(0)\n",
    "b = (booking.merge(extra[['booking_id', 'amount']], \n",
    "                   on='booking_id', how='left'))\n",
    "b['income'] = b['amount'].fillna(0)\n",
    "\n",
    "a = pd.concat([a[['booking_date', 'nights', 'income']],\n",
    "               b[['booking_date', 'nights', 'income']]])\n",
    "a['checkout_date'] = a['booking_date'] + \\\n",
    "    pd.to_timedelta(a['nights'], unit='D')\n",
    "a['wkd'] = a['checkout_date'].dt.weekday\n",
    "\n",
    "# weekday <= 3\n",
    "cond1 = a['checkout_date'].dt.weekday <= 3\n",
    "a.loc[cond1, 'Thursday'] = a.loc[cond1, 'checkout_date'] + \\\n",
    "    (pd.to_timedelta(3 - a.loc[cond1, 'wkd'], unit='D'))\n",
    "# weekday >=4\n",
    "cond2 = a['checkout_date'].dt.weekday > 3\n",
    "a.loc[cond2, 'Thursday'] = a.loc[cond2, 'checkout_date'] + \\\n",
    "    (pd.to_timedelta(10 - a.loc[cond2, 'wkd'], unit='D'))\n",
    "\n",
    "# a\n",
    "a.merge(thur, on='Thursday', how='right').groupby('Thursday')[['income']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
